﻿using System.Data.SqlClient;
using System.Data;
using System;

namespace Prassi2ExpertUpTransfer
{
    public sealed class ConnettiExpUpDB
    {
        static ConnettiExpUpDB instanza = null;

        SqlConnection connessione = null;

        private ConnettiExpUpDB()
        {
            connessione = new SqlConnection(
                "Data Source=10.3.2.4; " +
                "Database=ExpertUP;" +
                "User ID=sa; " +
                "Password=gestionale_2008; " +
                "Trusted_Connection=False; "
                    );

        }


        public static ConnettiExpUpDB Instanza
        {
            get
            {
                if (instanza == null) instanza = new ConnettiExpUpDB();
                return instanza;
            }
        }

        public SqlConnection GetDBConnection()
        {
            if (connessione.State != ConnectionState.Open)
            {
                connessione.Open();
            }
            return connessione;
        }

        public void CloseConnection()
        {
            if (connessione.State == ConnectionState.Open)
            {
                connessione.Close();
            }
        }

        public Int32 getCodCliente(int codiceDitta, string partitaIva, string codFiscale)
        {

            int codAnagrafico = ConnettiExpUpDB.Instanza.getCodAnagrafico(partitaIva, codFiscale);
            int valore = 0;
            string cmd_sql = null;
            SqlConnection myConnection = null;
            SqlCommand myCommand = null;
            SqlDataReader myreader = null;

            int valoreOutput = 0;

            try
            {
                myConnection = GetDBConnection();

                // Cerco con partita iva e codice fiscale
                cmd_sql = "select * from CLIA0000 where" +
                             " Entita=" + codiceDitta +
                             " And " +
                             " CodAnagrafico=" + codAnagrafico;
                myCommand = new SqlCommand(cmd_sql, myConnection);

                myreader = myCommand.ExecuteReader();

                while (myreader.Read())
                {
                    valore = myreader.GetInt32(5); // colonna (5) "Sottoc"
                    if (valore > 0)
                    {
                        valoreOutput = valore;
                        break;
                    }
                }

                myreader.Close();
                myCommand.Dispose();
                // se arrivo qui non trovato



            }
            catch (Exception err)
            {
                // TODO Exception AccessoDBException
                throw new Eccezioni.AccessoDBException(cmd_sql, err);
            }


            return valoreOutput;
        }


        private int getCodAnagrafico(string partitaIva, string codFiscale)
        {
            int valore = 0;
            string cmd_sql = null;
            SqlConnection myConnection = null;
            SqlCommand myCommand = null;
            SqlDataReader myreader = null;

            int valoreOutput = 0;

            try
            {
                myConnection = GetDBConnection();

                // Cerco con partita iva e codice fiscale
                cmd_sql = "select * from ANAA0000 where" +
                             " PartitaIva='" + partitaIva + "'" +
                             " And " +
                             " CodiceFiscale='" + codFiscale + "'" +
                             " ORDER BY DataInizioValidita DESC;";
                myCommand = new SqlCommand(cmd_sql, myConnection);

                myreader = myCommand.ExecuteReader();

                while (myreader.Read())
                {
                    valore = myreader.GetInt32(0); // colonna (0) "CodAnagrafico"
                    if (valore > 0)
                    {
                        valoreOutput = valore;
                        break;
                    }
                }

                myreader.Close();
                myCommand.Dispose();

                // se arrivo qui non trovato

            }
            catch (Exception err)
            {
                // TODO Exception AccessoDBException
                throw new Eccezioni.AccessoDBException(cmd_sql, err);
            }

            return valoreOutput; ;
        }
 



        public string getValue(string column, string table, string where)
        {

            string valore = null;
            string cmd_sql = "select " + column + " from " + table + " " + where;
            try
            {
                SqlConnection myConnection = GetDBConnection();

                SqlCommand myCommand = new SqlCommand(cmd_sql, myConnection);

                valore = Convert.ToString(myCommand.ExecuteScalar());

                myCommand.Dispose();

            }
            catch (Exception err)
            {
                // TODO Exception AccessoDBException
                throw new Eccezioni.AccessoDBException(cmd_sql, err);
            }

            return valore;
        }

        public int getValueInt(string column, string table, string where)
        {

            int valore = 0;
            try
            {
                Int32.TryParse(getValue(column, table, where), out valore);
            }
            catch (Exception err)
            {
                // TODO Exception rilanciata
                throw err;
            }

            return valore;
        }
    }
}